{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "720b40c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from pytz import timezone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "09505a86",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_unix_to_eastern(input_csv, timestamp_column, output_csv):\n",
    "    # Define the time zones\n",
    "    utc_tz = timezone('UTC')\n",
    "    eastern_tz = timezone('US/Eastern')\n",
    "\n",
    "    # Load the CSV file\n",
    "    df = pd.read_csv(input_csv)\n",
    "\n",
    "    # Convert the Unix timestamp (in milliseconds) to a datetime object with the correct UTC timezone\n",
    "    df['utc_datetime'] = pd.to_datetime(df[timestamp_column], unit='ms', origin='unix').dt.tz_localize(utc_tz)\n",
    "\n",
    "    # Convert the timestamp to Eastern Time\n",
    "    df['eastern_datetime'] = pd.to_datetime(df[timestamp_column], unit='ms', origin='unix').dt.tz_localize(utc_tz).dt.tz_convert(eastern_tz)\n",
    "    \n",
    "    # Format the UTC timestamp as 'YMDhms'\n",
    "    df['utc_datetime'] = df['utc_datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "    # Format the Eastern Time timestamp as 'YMDhms'\n",
    "    df['eastern_datetime'] = df['eastern_datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "    # Save the dataframe to a new CSV file\n",
    "    df.to_csv(output_csv, index=False)\n",
    "    \n",
    "    \n",
    "def convert_utc_to_eastern(input_csv, timestamp_column, output_csv):\n",
    "    # Define the time zones\n",
    "    utc_tz = timezone('UTC')\n",
    "    eastern_tz = timezone('US/Eastern')\n",
    "\n",
    "    # Load the CSV file\n",
    "    df = pd.read_csv(input_csv)\n",
    "\n",
    "   # Convert the timestamp column to a datetime object\n",
    "    df[timestamp_column] = pd.to_datetime(df[timestamp_column])\n",
    "    \n",
    "    # Check if the column is timezone-aware\n",
    "    if df[timestamp_column].dt.tz is None:\n",
    "        # If timezone-naive, localize to UTC\n",
    "        df[timestamp_column] = df[timestamp_column].dt.tz_localize('UTC')\n",
    "    else:\n",
    "        # If already timezone-aware, ensure it's in UTC\n",
    "        df[timestamp_column] = df[timestamp_column].dt.tz_convert('UTC')\n",
    "\n",
    "\n",
    "    # Create a new column for the formatted UTC timestamps\n",
    "    df['utc_datetime'] = df[timestamp_column].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "    # Convert the UTC timestamp to Eastern Time and format it\n",
    "    df['eastern_datetime'] = df[timestamp_column].dt.tz_convert(eastern_tz)\n",
    "    df['eastern_datetime'] = df['eastern_datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "\n",
    "    # Save the dataframe to a new CSV file\n",
    "    df.to_csv(output_csv, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b18adb8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/9v/n7f9f2f52ggc6g54m7hv7xwr0000gn/T/ipykernel_15472/3909347573.py:34: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  df[timestamp_column] = pd.to_datetime(df[timestamp_column])\n"
     ]
    }
   ],
   "source": [
    "# Define base directories\n",
    "BASE_DIR = \"/path/to/build\"\n",
    "INPUT_DIR = os.path.join(BASE_DIR, \"input\")\n",
    "OUTPUT_DIR = os.path.join(BASE_DIR, \"output\")\n",
    "\n",
    "# Lending Rates\n",
    "lending_rates = [\n",
    "    {\n",
    "        \"input\": os.path.join(OUTPUT_DIR, \"lending_rates_bitfinex_pseudo.csv\"),\n",
    "        \"output\": os.path.join(OUTPUT_DIR, \"lending_rates_bitfinex_eastern.csv\"),\n",
    "        \"is_unix\": True,\n",
    "    },\n",
    "    {\n",
    "        \"input\": os.path.join(OUTPUT_DIR, \"lending_rates_ftx_pseudo.csv\"),\n",
    "        \"output\": os.path.join(OUTPUT_DIR, \"lending_rates_ftx_eastern.csv\"),\n",
    "        \"is_unix\": False,\n",
    "    },\n",
    "]\n",
    "\n",
    "for rate in lending_rates:\n",
    "    if rate[\"is_unix\"]:\n",
    "        convert_unix_to_eastern(rate[\"input\"], \"timestamp\", rate[\"output\"])\n",
    "    else:\n",
    "        convert_utc_to_eastern(rate[\"input\"], \"time\", rate[\"output\"])\n",
    "\n",
    "# 2. Intraday Prices\n",
    "intraday_input = os.path.join(INPUT_DIR, \"cryptocompare\", \"tether_hourly_pseudo.csv\")\n",
    "intraday_output = os.path.join(OUTPUT_DIR, \"tether_hourly_eastern.csv\")\n",
    "convert_utc_to_eastern(intraday_input, \"date\", intraday_output)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "leverage_and_stablecoin_pegs",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
